package GUIThing;

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import GUIThing.*;

/**
 * Real doozy of a databaser here. Really all we do here is call the database
 * using code from Dale's BoxOfficeDB, except split up into three methods so
 * I can fill up each field as needed.
 * 
 * @author Alex
 *
 */
public class EventDB extends PostgresDB {

	private EventDB instance;

	/**
	 * EventDB() - WAY bigger a constructor than I thought!
	 * What we do here is set up our database connection. Short and sweet.
	 * 
	 * @param hostName - Host name of the Database. Usually an IP.
	 * @param port - Port number needed for connection.
	 * @param dbName - Name of the actual database schema we're going to use.
	 * @param userName - Username part of SQL login.
	 * @param password - Password part of SQL login.
	 * @throws ClassNotFoundException - In case we can't find a necessary class.
	 * @throws DatabaseTimeoutException - Our database timed out before we could get our data.
	 * @throws EventDBException - General exception, basically grab everything that breaks.
	 * @throws SQLException - SQL exceptions are bad, malformed data or just a bad connection.
	 */
	public EventDB(String hostName, int port, String dbName, String userName,
			String password) throws ClassNotFoundException,
			DatabaseTimeoutException, EventDBException, SQLException {
		super(hostName, port, dbName, userName, password);

		instance = this;
	}

	/**
	 * venueRun() - This is the data grabber for our venues JComboBox.
	 * The data sent from here is used to fill it with options using the database.
	 * 
	 * @return An ArrayList of strings with the theatre names from the database.
	 * @throws SQLException - In case our SQL statement breaks or the connection dies.
	 * @throws DatabaseTimeoutException - Our connection timed out before we could get our data!
	 */
	public List<String> venueRun() throws SQLException, DatabaseTimeoutException {
		List<String> venues = new ArrayList<>();
		

		Statement query;
		ResultSet results;

		instance.getConnection();

		if (db == null) {
			throw (new EventDBException("Something's wrong with the database, "
					+ "we have no connection."));
		}

		try {
			query = db.createStatement();
		} catch (SQLException e) {
			throw (new EventDBException("Creating our SQL statement didn't"
					+ " work, reason: ", e));
		}

		try {
			results = getResultsWithTimeout("SELECT * FROM venues", 10);
		} catch (SQLException e) {
			throw (new EventDBException("We couldn't make our "
					+ "query work, reason: ", e));
		}
		
		try {
			while (results.next()) {
				venues.add(new String(results.getString(2)));
			}
		} catch (SQLException e) {
			throw (new EventDBException(
					"Couldn't parse the results from our "
							+ "query, reason: ", e));
		}
		
		return venues;
	}
	

	/**
	 * eventTypeRun() - This is the data grabber for our eventType JComboBox.
	 * The data sent from here is used to fill it with options using the database.
	 * 
	 * @return An ArrayList of strings with the forms of entertainment from the database.
	 * @throws SQLException - In case our SQL statement breaks or the connection dies.
	 * @throws DatabaseTimeoutException - Our connection timed out before we could get our data!
	 */
	public List<String> eventTypeRun() throws DatabaseTimeoutException, SQLException {

		List<String> eventTypes = new ArrayList<>();

		Statement query;
		ResultSet results;

		instance.getConnection();

		if (db == null) {
			throw (new EventDBException("Something's wrong with the database, "
					+ "we have no connection."));
		}

		try {
			query = db.createStatement();
		} catch (SQLException e) {
			throw (new EventDBException("Creating our SQL statement didn't"
					+ " work, reason: ", e));
		}

		try {
			results = getResultsWithTimeout("SELECT * FROM eventtypes", 10);
		} catch (SQLException e) {
			throw (new EventDBException("We couldn't make our "
					+ "query work, reason: ", e));
		}
		
		try {
			while (results.next()) {
				eventTypes.add(new String(results.getString(2)));
			}
		} catch (SQLException e) {
			throw (new EventDBException(
					"Couldn't parse the results from our "
							+ "query, reason: ", e));
		}
		
		return eventTypes;
	}
	
	
	/**
	 * dataRun() - The big daddy of the data runners. This will grab all the information
	 * we need to fill our search results box. Grabs the ENTIRE table and dumps it into
	 * a local array for use.
	 * 
	 * @return An ArrayList of strings with event details from the database.
	 * @throws SQLException - In case our SQL statement breaks or the connection dies.
	 * @throws DatabaseTimeoutException - Our connection timed out before we could get our data!
	 */
	public List<Event> dataRun() throws DatabaseTimeoutException,
			EventDBException, SQLException {

		List<Event> eventsFromSQL = new ArrayList<>();
		Statement query;
		ResultSet results;

		instance.getConnection();

		if (db == null) {
			throw (new EventDBException("Something's wrong with the database, "
					+ "we have no connection."));
		}

		try {
			query = db.createStatement();
		} catch (SQLException e) {
			throw (new EventDBException("Creating our SQL statement didn't"
					+ " work, reason: ", e));
		}

		try {
			results = getResultsWithTimeout("SELECT * FROM performances", 10);
		} catch (SQLException e) {
			throw (new EventDBException("We couldn't make our "
					+ "query work, reason: ", e));
		}
		            
		while (results.next()) {
			try {
				GregorianCalendar[] gDates;
				try {
					java.sql.Array dateArray = results.getArray("times");
					java.sql.Timestamp[] rsDates = (java.sql.Timestamp[]) dateArray.getArray();
					gDates = new GregorianCalendar[rsDates.length];
					for (int i=0; i < rsDates.length; i++) {
						gDates[i] = new GregorianCalendar();
						gDates[i].setTime(rsDates[i]);
					}

				} catch (SQLException ex) {
					throw new BoxOfficeParserException("Unable to get date array", ex);
				}
				            
				float[] prices;
				try {
					java.sql.Array priceArray = results.getArray("prices");
					prices = (float []) priceArray.getArray();

				} catch (SQLException ex) {
					throw new BoxOfficeParserException("Unable to get price array", ex);
				}
				for (GregorianCalendar calendar : gDates) {
					String type = "All";
					String venue = "All";
					
					if (results.getString(4).equals("1")) {
						type = "Dance";
					}
					
					if (results.getString(4).equals("2")) {
						type = "Music";
					}
					
					if (results.getString(4).equals("3")) {
						type = "Theatre";
					}
					
					if (results.getString(4).equals("4")) {
						type = "Opera";
					}
					
					if (results.getString(5).equals("1")) {
						venue = "McPherson Playhouse";
					}
					
					if (results.getString(5).equals("2")) {
						venue = "Royal Theatre";
					}
					
					if (results.getString(5).equals("3")) {
						venue = "Belfry Theatre";
					}
					
					if (results.getString(5).equals("4")) {
						venue = "Save-On-Foods Memorial Centre";
					}
					
					eventsFromSQL.add(new Event(type, venue, results.getString(2), results.getString(3), calendar, prices));
					System.out.println("Type: " + type + " Venue: " + venue + " Name: " + results.getString(2) + " Description: " + results.getString(3));
				}
			} catch (SQLException e) {
				throw (new EventDBException(
					"Couldn't parse the results from our "
							+ "query, reason: ", e));
				}
			}

		return eventsFromSQL;
	}
}
